﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;

namespace H2TWebService
{
    /// <summary>
    /// Summary description for WebService1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class WebService1 : System.Web.Services.WebService
    {
        DatabaseProviderFactory factory = new DatabaseProviderFactory();
        Database northwind;
         
        [WebMethod]
        public Boolean Login(string username, string pass)
        {
            DataTable db = new DataTable("product");
            
            northwind = factory.Create("ConStringDemo");
            // Create a suitable command type and add the required parameter.
            using (DbCommand sprocCmd = northwind.GetStoredProcCommand("sp_getlogin"))
            {
                northwind.AddInParameter(sprocCmd, "username", DbType.String, username);
                northwind.AddInParameter(sprocCmd, "password", DbType.String, pass);

                // Call the ExecuteReader method with the command.
                using (IDataReader sprocReader = northwind.ExecuteReader(sprocCmd))
                {
                    try
                    {
                        sprocReader.Read();
                        string name = (string)sprocReader[0];
                        if (name != "")
                        {
                            return true;
                        }
                    }
                    catch (Exception ex)
                    {
                        return false;
                    }
                }
            }


            return false;
        }
        #region "Hoang code"
        [WebMethod]
        public DataTable productForOrder(int pagenum, int rowspage)
        {
            northwind = factory.Create("ConStringDemo");
            DataTable db = new DataTable();
            db = MakeNamesTable();
            using (DbCommand sprocCmd = northwind.GetStoredProcCommand("sp_getProductForOder"))
            {
                northwind.AddInParameter(sprocCmd, "@PageNumber", DbType.String, pagenum);
                northwind.AddInParameter(sprocCmd, "@RowspPage", DbType.String, rowspage);

                // Call the ExecuteReader method with the command.
                using (IDataReader sprocReader = northwind.ExecuteReader(sprocCmd))
                {
                    DataTable dbtemp = new DataTable();
                    dbtemp.Load(sprocReader);

                    foreach (DataRow rows in dbtemp.Rows)
                    {
                        DataRow newrow = db.NewRow();
                        newrow["num"] = rows["NUMBER"];
                        newrow["id"] = rows["id"];
                        newrow["Product Name"] = rows["Product Name"].ToString();
                        newrow["Category Name"] = rows["Category Name"].ToString();
                        newrow["Price"] = rows["Price"];
                        newrow["Quantity per Unit"] = rows["Quantity per Unit"].ToString();
                        db.Rows.Add(newrow);
                    }
                }
            }

            return db;
        }

        private DataTable MakeNamesTable()
        {
            // Create a new DataTable titled 'Names.'
            DataTable namesTable = new DataTable("product");

            // Add three column objects to the table.
            DataColumn numColumn = new DataColumn();
            numColumn.DataType = System.Type.GetType("System.Int32");
            numColumn.ColumnName = "num";
            namesTable.Columns.Add(numColumn);

            DataColumn idColumn = new DataColumn();
            idColumn.DataType = System.Type.GetType("System.Int32");
            idColumn.ColumnName = "id";
            namesTable.Columns.Add(idColumn);

            DataColumn fNameColumn = new DataColumn();
            fNameColumn.DataType = System.Type.GetType("System.String");
            fNameColumn.ColumnName = "Product Name";
            fNameColumn.DefaultValue = "Fname";
            namesTable.Columns.Add(fNameColumn);

            DataColumn lNameColumn = new DataColumn();
            lNameColumn.DataType = System.Type.GetType("System.String");
            lNameColumn.ColumnName = "Category Name";
            namesTable.Columns.Add(lNameColumn);

            DataColumn priceColumn = new DataColumn();
            priceColumn.DataType = System.Type.GetType("System.Int32");
            priceColumn.ColumnName = "Price";
            namesTable.Columns.Add(priceColumn);

            DataColumn unitColumn = new DataColumn();
            unitColumn.DataType = System.Type.GetType("System.String");
            unitColumn.ColumnName = "Quantity per Unit";
            namesTable.Columns.Add(unitColumn);

            // Create an array for DataColumn objects.
            //DataColumn[] keys = new DataColumn[1];
            //keys[0] = idColumn;
            //namesTable.PrimaryKey = keys;

            // Return the new DataTable. 
            return namesTable;
        }


        [WebMethod]
        public List<Customer> LoginWithCustomer(string username, string pass)
        {
            northwind = factory.Create("ConStringDemo");
            // Create a default output row mapper 
            IRowMapper<Customer> rowMapper = MapBuilder<Customer>.BuildAllProperties();
            // out put map tu thay doi
            //IRowMapper<Customer> rowMapper = MapBuilder<Customer>.MapAllProperties()
            //    .MapByName(x => x.CustomerId)
            //    .DoNotMap(x => x.ContactName)
            //    .Build();
            // Create a sproc accessor that uses a custom parameter mapper
            IParameterMapper paramMapper = new CustomerParameterMapper();
            //// cach 1 create accessor ma ko can database truoc
            //var accessor = new SprocAccessor<Customer>(northwind, "sp_GetLoginByCustomer", paramMapper, rowMapper);

            //// cach 2 create accessor bang database
           // var accessor = northwind.CreateSprocAccessor<Customer>("sp_GetLoginByCustomer", paramMapper,rowMapper);
            // Use a custom parameter mapper and a custom output mapper           
            //var results = accessor.Execute("admin", "admin");
            
            // cach 3 thuc thi accessor truc tiep ma ko can khoi tao
            var results = northwind.ExecuteSprocAccessor<Customer>("sp_GetLoginByCustomer", paramMapper, rowMapper, username,pass);
          
            return results.ToList();
        }
        #endregion
      #region "Tuyen's code"
        [WebMethod]
        public List<Category> GetCategory(string categoryName)
        {

        }
      #endregion
    }
}
